package common.util.generate.dao.impl;

import common.util.generate.dao.JdbcDao;
import common.util.generate.properties.ParamProperties;
import common.util.generate.properties.TableFieldProperties;
import common.util.generate.properties.TableProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author
 *
 * @description 针对information_schema数据库中存储的本项目表的基本信息来自动生成代码
 */
@Repository
public class JdbcDaoImpl implements JdbcDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;


    @Override
    public TableProperties queryOracleTableInfo(String tableName) {
        String sql =
                "select " +
                "       a.table_name tableName, " +
                "       a.comments comments " +
                "   from user_tab_comments a " +
                "   where " +
                "       a.table_type = 'TABLE' " +
                "       and a.table_name = ? ";

        Map<String, Object> map = jdbcTemplate.queryForMap(sql, new Object[]{tableName});
        TableProperties table = new TableProperties();
        table.setTableName((String) map.get("tableName"));
        table.setComments((String) map.get("comments"));
        return table;
    }

    @Override
    public TableProperties queryMysqlTableInfo(String tableName) {
        String sql =
                "select " +
                "   TABLE_NAME  AS tableName," +
                "   TABLE_COMMENT as comments" +
                "   from information_schema.tables " +
                "  where " +
                "   TABLE_NAME=? ";
        Map<String, Object> map = jdbcTemplate.queryForMap(sql, new Object[]{tableName});
        TableProperties table = new TableProperties();
        table.setTableName((String) map.get("tableName"));
        table.setComments((String) map.get("comments"));
        return table;
    }

    @Override
    public List<TableFieldProperties> queryOracleTableFiledProperties(String tableName, ParamProperties param) {
        String sql  =
                "SELECT " +
                "       a.column_name fieldName," +
                "       a.data_type fieldType, " +
                "       a.data_length maxLength, " +
                "       a.nullable isNull, " +
                "       a.data_default dataDefault, " +
                "       b.comments comments, " +
                "       a.data_precision dataPrecision, " +
                "       a.data_scale dataScale , " +
                "       a.default_length defaultLength"+
                "   FROM user_tab_columns a LEFT JOIN user_col_comments b " +
                "           ON a.TABLE_NAME = b.table_name  AND a.COLUMN_name = b.COLUMN_name  " +
                "   WHERE " +
                "       a.table_name = ? ";
        List<Map<String, Object>> map = jdbcTemplate.queryForList(sql, new Object[]{tableName});
        List<TableFieldProperties> fields = new ArrayList<>();
        for (Map<String, Object> fieldsRs : map) {
            TableFieldProperties field = new TableFieldProperties();
            field.setFieldName((String) fieldsRs.get("fieldName"));
            field.setFieldType((String) fieldsRs.get("fieldType"), param);
            BigDecimal maxLength = (BigDecimal) fieldsRs.get("maxLength");
            field.setMaxLength(maxLength == null ? null : maxLength.intValue());
            field.setNull("n".equalsIgnoreCase((String)fieldsRs.get("isNull")));
            field.setDataDefault(fieldsRs.get("dataDefault"));
            field.setComments((String) fieldsRs.get("comments"));
            BigDecimal dataPrecision = (BigDecimal) fieldsRs.get("dataPrecision");
            field.setDataPrecision(null == dataPrecision?null : dataPrecision.intValue());
            BigDecimal dataScale = (BigDecimal) fieldsRs.get("dataScale");
            field.setDataScale(dataScale==null?null:dataScale.intValue() );
            BigDecimal defaultLength = (BigDecimal) fieldsRs.get("defaultLength");
            field.setDefaultLength(defaultLength==null ? null:defaultLength.intValue() );
            fields.add(field);
        }
        return fields;

    }

    @Override
    public List<TableFieldProperties> queryMysqlTableFiledProperties(String tableName, ParamProperties param) {
        String sql  =
                "SELECT " +
                "       COLUMN_NAME fieldName," +
                "       DATA_TYPE fieldType, " +
                "       CHARACTER_MAXIMUM_LENGTH maxLength, " +
                "       CASE WHEN IS_NULLABLE='NO' THEN 'n' ELSE 'y' END isNull, " +
                "       COLUMN_DEFAULT dataDefault, " +
                "       COLUMN_COMMENT comments, " +
                "       NUMERIC_PRECISION dataPrecision, " +
                "       NUMERIC_SCALE dataScale  " +
                "   FROM information_schema.`COLUMNS` " +
                "   WHERE " +
                "       table_name = ? ";
        List<Map<String, Object>> map = jdbcTemplate.queryForList(sql, new Object[]{tableName});
        List<TableFieldProperties> fields = new ArrayList<>();
        for (Map<String, Object> fieldsRs : map) {
            TableFieldProperties field = new TableFieldProperties();
            field.setFieldName((String) fieldsRs.get("fieldName"));
            field.setFieldType((String) fieldsRs.get("fieldType"), param);
            BigInteger maxLength = (BigInteger) fieldsRs.get("maxLength");
            field.setMaxLength(maxLength == null ? null : maxLength.intValue());
            field.setNull("n".equalsIgnoreCase((String)fieldsRs.get("isNull")));
            field.setDataDefault(fieldsRs.get("dataDefault"));
            field.setComments((String) fieldsRs.get("comments"));
            BigInteger dataPrecision = (BigInteger) fieldsRs.get("dataPrecision");
            field.setDataPrecision(null == dataPrecision?null : dataPrecision.intValue());
            BigInteger dataScale = (BigInteger) fieldsRs.get("dataScale");
            field.setDataScale(dataScale==null?null:dataScale.intValue() );
            BigInteger defaultLength = (BigInteger) fieldsRs.get("defaultLength");
            field.setDefaultLength(defaultLength==null ? null:defaultLength.intValue() );
            fields.add(field);
        }
        return fields;
    }

    @Override
    public List<String> queryOraclePrimaryKeyFileds(String tableName) {
        String sql =
                "SELECT " +
                "       col.column_name pkFieldName" +
                "     FROM user_constraints con LEFT JOIN user_cons_columns col " +
                "       ON con.constraint_name = col.constraint_name " +
                "     WHERE " +
                "       con.constraint_type = 'P' " +
                "       AND col.table_name = ? " ;
        return jdbcTemplate.queryForList(sql, new Object[]{tableName},String.class);
    }

    @Override
    public List<String> queryMysqlPrimaryKeyFileds(String tableName) {
        String sql =
                "SELECT " +
                "       COLUMN_NAME pkFieldName" +
                "     FROM information_schema.`COLUMNS` " +
                "     WHERE " +
                "        COLUMN_KEY='PRI' " +
                "       AND table_name = ? " ;
        return jdbcTemplate.queryForList(sql, new Object[]{tableName},String.class);
    }
}
